/**
 * 商家食品管理
 */
const express = require('express')
const storegoods = express.Router()

// 引入数据库模块
const db = require('../../utils/db')

// 导入文件上传解析模块
const getformidable = require('../../utils/myformidable')
const fs = require('fs')

// 导入上传远程仓库的工具函数
const myoss = require('../../utils/myoss')

// 指定开启的导航
const curNav = 4
// 图片的基准路径
const base_path_pic = 'http://admi-yuliner.oss-cn-hangzhou.aliyuncs.com/shopgoods/'

// 商家食品列表
storegoods.get('/goodslist', async (req, res) => {
   const totalNum = await getTatalCount()
   const keys = 'goods.id,goods.foodname,goods.foodpic,goods.descr,goods.price,shoplists.shopname'
   const sql = `SELECT ${keys} FROM goods,shoplists WHERE goods.shoplist_id=shoplists.id`
   db.query(sql, (err, result) => {
      res.render('StoreGoods/goodslist', {
         curNav: curNav,
         user: req.session.uname,
         data: result,
         host: base_path_pic,
         totalNum
      })
   })
})
function getTatalCount() {
   return new Promise((resolve, reject) => {
      db.query(`SELECT COUNT(*) FROM goods`, (err, result) => {
         if (result.length > 0) return resolve(result[0]['COUNT(*)'])
         resolve('')
      })
   })
}

// 食品添加
storegoods.get('/goodsadd', async (req, res) => {
   const shopNames = await getOtherShopname()
   res.render('StoreGoods/goodsadd', {
      curNav,
      user: req.session.uname,
      host: base_path_pic,
      shopNames // 已入住的店铺名称
   })
})
storegoods.post('/goodsdoadd', async (req, res) => {
   const { params, files } = await getformidable(req)
   // 获取店铺文字类信息参数
   const { foodname, foodpic, descr, price, shopname } = params
   // 获取图片参数
   const { filepath, newFilename, size } = files.newfoodpic

   // 获取食品对应店铺的 id
   const shoplist_id = await getshoplistId(shopname)

   if (size > 0 && shoplist_id) {
      // 需上传图片到远程仓库
      const sql = `INSERT INTO goods(foodname,descr,price,shoplist_id,foodpic) VALUES('${foodname}','${descr}','${price}',${shoplist_id},'${newFilename}')`
      // 将数据添加到数据库
      db.query(sql, async (err, result) => {
         if (result.affectedRows > 0) {
            await myoss.pushdata('shopgoods/' + newFilename, filepath) // 上传图片
            fs.unlinkSync(filepath) // 删除本地图片
            res.send(`<script>alert('添加成功');window.location.href='/storegoods/goodslist'</script>`)
         } else {
            fs.unlinkSync(filepath) // 删除本地图片
            res.send(`<script>alert('添加失败');window.location.href='/storegoods/goodsadd'</script>`)
         }
      })

   } else if (shoplist_id) {
      // 直接添加文字类数据到本地数据库
      const sql = `INSERT INTO goods(foodname,descr,price,shoplist_id,foodpic) VALUES('${foodname}','${descr}','${price}',${shoplist_id},'${foodpic}')`
      db.query(sql, (err, result) => {
         if (result.affectedRows > 0) {
            fs.unlinkSync(filepath) // 删除本地图片
            res.send(`<script>alert('添加成功');window.location.href='/storegoods/goodslist'</script>`)
         } else {
            fs.unlinkSync(filepath) // 删除本地图片
            res.send(`<script>alert('添加失败');window.location.href='/storegoods/goodsadd'</script>`)
         }
      })

   } else {
      // 未找到该店铺
      fs.unlinkSync(filepath) // 删除本地图片
      res.send(`<script>alert('该店铺未入住，先去入驻吧');window.location.href='/storegoods/goodsadd'</script>`)
   }
})
function getshoplistId(shopname) {
   return new Promise((resolve, reject) => {
      // 获取对应店铺的 id
      const sql = `SELECT id FROM shoplists WHERE shopname='${shopname}'`
      db.query(sql, (err, result) => {
         if (err) reject(err)
         const id = result.length !== 0 ? result[0].id : null
         resolve(id)
      })
   })
}

// 食品更新
storegoods.get('/goodsupdate', async (req, res) => {
   const {id, shopname} = req.query
   const otherShaopName = await getOtherShopname()
   const sql = `SELECT * FROM goods WHERE id = ${id}`
   db.query(sql, (err, result) => {
      if (result.length <= 0) return res.end('not found')
      res.render('StoreGoods/goodsupdate', {
         curNav,
         user: req.session.uname,
         host: base_path_pic,
         data: result[0],
         otherShaopName,
         shopname // 所属商家
      })
   })
})
function getOtherShopname() {
   return new Promise((resolve, reject) => {
      const sql = 'SELECT shopname FROM shoplists'
      db.query(sql, (err, data) => {
         if (data.length > 0) {
            resolve(data)
         } else {
            resolve([])
         }
      })
   })
}
storegoods.post('/goodsdoupdate', async (req, res) => {
   const {params, files} = await getformidable(req)
   // 获取参数列表
   const {id, foodname, descr, price, shopname, shoplist_id, foodpic} = params
   // 获取图片参数
   const {filepath, newFilename, size} = files.newfoodpic

   const [newId, newShopName] = await getShopnameId(shopname)
   if (!newId) {
      fs.unlinkSync(filepath) // 删除本地新图
      res.send(`<script>alert('所属店铺不存在');window.location.href='/storegoods/goodsupdate?id=${id}&shopname=${newShopName}'</script>`)
      return
   }
   
   if (size > 0) {
      // 更新远程图片和数据库信息
      const sql = `UPDATE goods SET foodname='${foodname}', descr='${descr}', price='${price}', shoplist_id=${newId}, foodpic='${newFilename}' WHERE id=${id}`
      db.query(sql, async (err, result) => {
         if (result.affectedRows > 0) {
            await myoss.deldata('shopgoods/' + foodpic) //删除云端旧图
            await myoss.pushdata('shopgoods/' + newFilename, filepath) //上传新图
            fs.unlinkSync(filepath) // 删除本地新图
            res.send(`<script>alert('更新成功');window.location.href='/storegoods/goodslist'</script>`)
         } else {
            fs.unlinkSync(filepath) // 删除本地新图
            res.send(`<script>alert('更新失败');window.location.href='/storegoods/goodsupdate?id=${id}&shopname=${shopname}</script>`)
         }
      })

   } else {
      // 更新数据库信息
      const sql = `UPDATE goods SET foodname='${foodname}', descr='${descr}', price='${price}', shoplist_id=${newId} WHERE id=${id}`
      db.query(sql, (err, result) => {
         if (result.affectedRows > 0) {
            res.send(`<script>alert('更新成功');window.location.href='/storegoods/goodslist'</script>`)
         } else {
            res.send(`<script>alert('更新失败');window.location.href='/storegoods/goodsupdate?id=${id}&shopname=${shopname}</script>`)
         }
      })
   }
})
function getShopnameId(shopname) {
   shopname = shopname.includes('(原店铺名)') ? shopname.slice(0, shopname.indexOf('(')) : shopname
   return new Promise((resolve, reject) => {
      const sql = `SELECT id FROM shoplists WHERE shopname='${shopname}'`
      db.query(sql, (err, result) => {
         if (result.length <= 0) {
            resolve([null, shopname])
         } else {
            resolve([result[0].id, shopname])
         }
      })
   })
}

// 食品删除
storegoods.get('/goodsdel', (req, res) => {
   const {id, foodpic} = req.query
   const sql = `DELETE FROM goods WHERE id=${id}`
   db.query(sql, async (err, result) => {
      if (result.affectedRows > 0) {
         if (foodpic !== 'default.webp') {
            // 删除远程仓库对应图片
            await myoss.deldata('shopgoods/' + foodpic)
         }
         res.json({msg: 'ok', status: 200})
      } else {
         res.json({msg: 'no', status: 404})
      }
   })
})

// 获取指定页码数据
storegoods.get('/getpagesdata', (req, res) => {
   const keys = 'goods.id,goods.foodname,goods.foodpic,goods.descr,goods.price,shoplists.shopname'
   const sql = `SELECT ${keys} FROM goods,shoplists WHERE goods.shoplist_id=shoplists.id`
   db.query(sql, (err, result) => {
      if (result.length > 0) {
         res.json({data: result, msg: 'ok'})
      } else {
         res.json({data: [], msg: 'no'})
      }
   })
})

// 搜索功能
storegoods.get('/search', async (req, res) => {
   const {kwd} = req.query
   let list = null
   const totalNum = await getTatalCount()
   const keys = 'goods.id,goods.foodname,goods.foodpic,goods.descr,goods.price,shoplists.shopname'
   const sql = `SELECT ${keys} FROM goods,shoplists WHERE goods.shoplist_id=shoplists.id`
   db.query(sql, (err, result) => {
      if (result.length > 0) {
         result = result.filter((item) => {
            return item.foodname.includes(kwd) || item.descr.includes(kwd) || item.shopname.includes(kwd)
         })
      }
      res.render('StoreGoods/goodslistsearch', {
         curNav: curNav,
         user: req.session.uname,
         data: result,
         host: base_path_pic,
         totalNum,
         // list
      })
   })
})

module.exports = storegoods